package com.wlyuan.open.scanner.dao.mapper;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.wlyuan.open.scanner.application.annotation.DataScope;
import com.wlyuan.open.scanner.dao.entity.OrderIndexDO;
import com.wlyuan.open.scanner.domain.quotation.QuotationOrder;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import java.util.List;

/**
 * <p>
 * Mapper 接口
 * </p>
 *
 * @author yuanjie
 * @since 2022-10-14
 */
public interface OrderIndexMapper extends BaseMapper<OrderIndexDO> {

    String QUERY_SQL_ID = "SELECT a.Index_ID from TMS_OrderIndex a ";
    String QUERY_SQL_SRC_CLASS = "SELECT a.Index_SrcClass from TMS_OrderIndex a ";
    String QUERY_COMPANY_ID = "SELECT a.Index_CreatorCompanyID from TMS_OrderIndex a ";

    String QUERY_SQL = "select a.Index_ID, a.Index_GUID, a.opt_status, a.Index_Code, a.Index_PactCode, a.Index_EndUserID, a.Index_EndUserName, a.Index_FromManID, a.Index_FromMan, a.Index_From, a.Index_FromProvince, " +
            "a.Index_FromCity, a.Index_FromDistrict, a.Index_FromTime, a.Index_ToManID, a.Index_ToMan, a.Index_To, a.Index_ToProvince, a.Index_ToCity, a.Index_ToDistrict, a.Index_ToTime, " +
            "a.Index_TransportMode, a.Index_GoodsCategory, a.Index_PackageMode, a.Index_ChargeMode, a.Index_PriceUnit, a.Index_Status, a.Index_StatusTime,a.Index_Status as transportStatus,a.Index_Status as combineStatus, a.Index_SrcOrderID, " +
            "a.Index_RootOrderID, a.Index_SrcClass, a.Index_Kms, a.Index_CarType, a.Index_CarLength, a.Index_DriverID, a.Index_CarID, a.Index_SupplierID, a.Index_SupplierCompanyID, a.Index_CustomerID, " +
            "a.Index_CustomerCompanyID, a.Index_ShipMode, a.Index_Pick, a.Index_Delivery, a.Index_Creator, a.Index_CreatorCompanyID, a.Index_CreateTime, a.Index_Confirmer, a.Index_ConfirmTime, case a.index_Singer when null then 0 else a.index_Singer end as indexSinger, " +
            "a.Index_SignTime, a.Index_ReceiptDoc, a.Index_Exception, a.Index_Invalid, a.Index_Comments, a.Index_OnLoad, a.Index_OffLoad, a.Index_Insurance, a.Index_VolumeAddition, a.Index_WeightAddition, " +
            "a.Index_Description, a.Index_CarVolume, a.Index_CarWeight, a.Index_Combined, a.Index_CustomerSymbolID, a.Index_SupplierSymbolID, a.Index_ReceiptDoc1, a.Index_ReceiptDoc2, a.Index_ReceiptDoc3, " +
            "a.Index_FromContact, a.Index_ToContact, a.Index_GoodsValue, a.Index_PrevOrderID, a.Index_DeviceCode, a.Index_Payment, a.Index_Payable, a.Index_GoodsLst, a.Index_RealFromTime, a.Index_RealToTime, " +
            "a.Index_ReceiptDoc4, a.Index_ReceiptDoc5, a.Index_ReceiptDoc6, a.Index_ReceiptDoc7, a.Index_RollBack, a.Index_ReceipTime, a.Index_FromOperator, a.Index_TerminalOrderID, a.Index_TerminalOrderCode, " +
            "a.Index_SupplierName, a.Index_CreatorCompanyName, a.Index_CustomerSymbolName, a.Index_SupplierSymbolName, " +
            "a.Index_CombinedAmount, a.Index_CombinedCost, a.Index_CombinedWeight, a.Index_CombinedVolume, a.Index_CombinedOrderAmount, a.Index_CombinedFrom, a.Index_CombinedTo, a.Index_TCacheReady," +
            "a.Index_StartMsgTime, a.Index_ArriveMsgTime, a.Index_VerifyCode, a.Index_DeviceCreatTime, a.Index_GPSStartTime, a.Index_DeviceBindingTime, a.Index_FromLocation, a.Index_ToLocation, a.Index_ContainsBillDay, " +
            "a.Index_TrackType, a.Index_SupplierType, a.Index_CusRollBack, a.Index_Fromtype, a.Index_Totype, a.Index_RollBackOrderID, a.Index_tpPrint, a.Index_ExceptionType, a.Index_AssessLevel, a.Index_AssessMent, " +
            "a.Index_CloseMark,a.Index_CloseMark as closeStatus, a.Index_CloseMent, a.Index_Addorder, a.Index_AddPacth, a.Index_CarCount, a.Index_AutoSchedule, a.Index_CombinePrice, a.Index_OrderType, a.Index_BeSplit, a.Index_SplitType, " +
            "a.Index_ReceiptDoc8, a.Index_ReceiptDoc9, a.Index_CloseInitiator, a.Index_CloseType, a.Index_signinfotype, a.Index_signinfo, a.Index_RollBckLst, a.Index_VerifySiginTime, a.Index_CreateType, " +
            "a.Index_ReceiptType, a.Index_additionTransportType, a.Index_WithChildType, a.Index_DeviceCode2, a.Index_Distance, a.Index_RealFromStatus, a.Index_DeviceBindingLog, a.Index_DeviceBindingPart, a.Index_Test," +
            "a.Index_echo," +
            "b.Index_CustomerName as indexCustomername,b.Index_TotalAmount as indexTotalamount,b.Index_TotalVolume as indexTotalvolume,b.Index_TotalWeight as indexTotalweight," +
            "b.Index_SupplierSymbolName,b.Index_SupplierName,b.Index_CombinedAmount,b.Index_CombinedOrderAmount," +
            "b.Index_OwnerCombinedOrderID as ownerCombinedOrderId,b.Cache_SignType as signType," +
            "b.Index_ToCity as toCityName,b.Index_ToProvince as toProvinceName,b.Index_ToDistrict as toDistrictName, b.Index_FromCity as fromCityName,b.Index_FromProvince as fromProvinceName," +
            "b.Index_FromDistrict as fromDistrictName, b.index_from as fromAddress,b.index_to as toAddress," +
            "b.Cache_LessCost as lessCost,b.Cache_FullCost as fullCost,b.Cache_PickCost as pickCost,b.Cache_DeliveryCost as deliveryCost,b.Cache_OnLoadCost as onLoadCost," +
            "b.Cache_OffLoadCost as offLoadCost,b.Cache_InsuranceCost as insuranceCost,b.Cache_TaxCost as taxCost,b.Cache_AdditionCost as additionCost,b.Index_Payment as payment," +
            "b.Index_Payable as payable,b.Index_Paysub as paysub,b.Cache_TotalCost as totalCost,b.Cache_MinCost as minCost,ISNULL(b.Cache_RefreshPrice ,0) as refreshPrice," +
            "b.Cache_Docid as docId, b.Cache_FNAStatus as financeStatus,b.Index_OwnerCombinedOrderID as ownerCombinedOrderId," +

            "c.User_Name as createManDesc," +
            "CASE when d.Customer_OwnerCompany=d.Customer_CompanyID THEN 2 ELSE d.Customer_UnderLine end as customerType,d.Customer_Name as customerName,d.Customer_OwnerCompany as downTenantId " +
            "from TMS_OrderIndex a ";

    String LEFT_JOIN = "LEFT JOIN TMS_User c on a.Index_Creator = c.User_ID " +
            "LEFT JOIN TMS_MCustomer d on a.Index_CustomerID = d.Customer_ID " +
            "LEFT JOIN TMS_OrderIndexCaches b on a.Index_ID=b.Caches_OrderID ";

    String ORDER_WHERE = "WHERE a.Index_SrcClass = 1 and a.Index_SrcOrderID = 0 and a.Index_Status <> -1 and a.Index_Status <> 0 ";

    String TRANSPORT_WHERE = "WHERE (a.Index_Status <> -1 and a.Index_Status <> 0 and a.Index_SrcClass = 2) or (a.Index_SrcClass = 1 and a.Index_SrcOrderID = 0)";

    String COMBINE_WHERE = "WHERE a.Index_SrcClass = 3 and a.Index_Status <> -1 and " +
            "EXISTS(select Contains_OwnerOrderID from TMS_OrderContains o left join TMS_OrderIndex i on o.Contains_OrderID = i.Index_ID where o.Contains_OwnerOrderID = a.Index_ID) ";

    String TENANT_COLUMN = "Index_CreatorCompanyID";

    String ORDER_BY = " ORDER BY a.Index_ID";

    String QUOTATION_TRANSPORT_SQL = "select a.Index_ID as orderId,a.Index_SrcClass as type,a.Index_SrcOrderID as srcOrderId,a.Index_CreatorCompanyID as tenantId,a.Index_TransportMode as transportMode, " +
            "b.Index_TotalAmount as totalQuantity,a.Index_GoodsValue as totalValue,b.Index_TotalVolume as totalVolume,b.Index_TotalWeight as totalWeight,b.Cache_TotalCost as totalCost,b.Cache_Docid as docId, " +
            "b.Cache_LessCost as lessCost,b.Cache_FullCost as fullCost,b.Cache_PickCost as pickCost,b.Cache_DeliveryCost as deliveryCost,b.Cache_OnLoadCost as onLoadCost," +
            "b.Cache_OffLoadCost as offLoadCost,b.Cache_InsuranceCost as insuranceCost,b.Cache_TaxCost as taxCost,b.Cache_AdditionCost as additionCost,b.Index_Payment as payment," +
            "b.Index_Payable as payable,b.Index_Paysub as paysub,b.Cache_TotalCost as totalCost,b.Cache_MinCost as minCost,ISNULL(b.Cache_RefreshPrice ,0) as refreshPrice, Index_Combined as combined " +
            "FROM TMS_OrderIndex a " +
            "LEFT JOIN TMS_OrderIndexCaches b on a.Index_ID=b.Caches_OrderID ";

    /**
     * 订单id集合
     *
     * @return
     */
    @Select(QUERY_SQL_ID + LEFT_JOIN + ORDER_WHERE + ORDER_BY + " OFFSET ( #{pageNum} -1)* #{pageSize} ROWS FETCH next #{pageSize} rows only")
    @DataScope(column = TENANT_COLUMN, alias = "a")
    List<Long> getOrderIds(@Param("pageNum") Integer pageNum, @Param("pageSize") Integer pageSize);

    /**
     * 查询订单列表
     *
     * @return
     */
    @Select(QUERY_SQL + LEFT_JOIN + ORDER_WHERE + ORDER_BY + " OFFSET ( #{pageNum} -1)* #{pageSize} ROWS FETCH next #{pageSize} rows only")
    @DataScope(column = TENANT_COLUMN, alias = "a")
    List<OrderIndexDO> list(@Param("pageNum") Integer pageNum, @Param("pageSize") Integer pageSize);

    @Select("SELECT count(*) from TMS_OrderIndex a " + LEFT_JOIN + ORDER_WHERE)
    @DataScope(column = TENANT_COLUMN, alias = "a")
    Integer count();

    @Select("SELECT count(*) from TMS_OrderIndex a " + LEFT_JOIN + ORDER_WHERE + " and a.Index_CreatorCompanyID = #{tenantId}")
    @DataScope(column = TENANT_COLUMN, alias = "a")
    Integer countByTenantId(@Param("tenantId") Long tenantId);

    @Select(QUERY_SQL_ID + LEFT_JOIN + ORDER_WHERE + " and a.Index_CreatorCompanyID = #{tenantId}" + ORDER_BY + " OFFSET ( #{pageNum} -1)* #{pageSize} ROWS FETCH next #{pageSize} rows only")
    @DataScope(column = TENANT_COLUMN, alias = "a")
    List<Long> getOrderIdsByTenantId(@Param("pageNum") Integer pageNum, @Param("pageSize") Integer pageSize, @Param("tenantId") Long tenantId);

    /**
     * 运单id集合
     *
     * @return
     */
    @Select(QUERY_SQL_ID + LEFT_JOIN + TRANSPORT_WHERE + ORDER_BY)
    @DataScope(column = TENANT_COLUMN, alias = "a")
    List<Long> getTransportIds();

    /**
     * 拼车单id集合
     *
     * @return
     */
    @Select(QUERY_SQL_ID + LEFT_JOIN + COMBINE_WHERE + ORDER_BY + " OFFSET ( #{pageNum} -1)* #{pageSize} ROWS FETCH next #{pageSize} rows only")
    @DataScope(column = TENANT_COLUMN, alias = "a")
    List<Long> combineIds(@Param("pageNum") Integer pageNum, @Param("pageSize") Integer pageSize);

    @Select("SELECT count(*) from TMS_OrderIndex a " + LEFT_JOIN + COMBINE_WHERE)
    @DataScope(column = TENANT_COLUMN, alias = "a")
    Integer combineCount();

    @Select("SELECT count(*) from TMS_OrderIndex a " + LEFT_JOIN + COMBINE_WHERE + " and a.Index_CreatorCompanyID = #{tenantId} ")
    @DataScope(column = TENANT_COLUMN, alias = "a")
    Integer combineCountByTenantId(@Param("tenantId") Long tenantId);

    @Select(QUERY_SQL_ID + LEFT_JOIN + COMBINE_WHERE + " and a.Index_CreatorCompanyID = #{tenantId} " + ORDER_BY + " OFFSET ( #{pageNum} -1)* #{pageSize} ROWS FETCH next #{pageSize} rows only")
    @DataScope(column = TENANT_COLUMN, alias = "a")
    List<Long> combineByTenantId(@Param("pageNum") Integer pageNum, @Param("pageSize") Integer pageSize, @Param("tenantId") Long tenantId);

    /**
     * 根据id查询
     *
     * @param orderId
     * @return
     */
    @Select(QUERY_SQL + LEFT_JOIN + "WHERE a.Index_ID = #{orderId}")
    OrderIndexDO getById(Long orderId);

    @Select(QUERY_SQL + LEFT_JOIN + " WHERE a.Index_SrcClass = 1 and a.Index_SrcOrderID = 0 and a.Index_Status <> -1 and a.Index_Status <> 0 and a.Index_ID = #{orderId}")
    OrderIndexDO getOrderById(Long orderId);

    /**
     * 根据来源id查询
     *
     * @param orderId
     * @return
     */
    @Select(QUERY_SQL + LEFT_JOIN + "WHERE a.Index_SrcOrderID = #{orderId} and a.Index_Status <> 32")
    List<OrderIndexDO> getBySrcOrderId(Long orderId);

    /**
     * 根据id获取报价订单
     *
     * @param orderId
     * @return
     */
    @Select(QUOTATION_TRANSPORT_SQL + " WHERE a.Index_ID = #{orderId}")
    @DataScope(column = TENANT_COLUMN, alias = "a")
    QuotationOrder getQuotationOrderByOrderId(Long orderId);

    /**
     * 找拆单子单
     *
     * @param prevOrderId
     * @return
     */
    @Select("SELECT top 1 a.Index_ID from TMS_OrderIndex a where a.Index_PrevOrderID = #{prevOrderId}")
    @DataScope(column = TENANT_COLUMN, alias = "a")
    Long getPrevOrderId(@Param("prevOrderId") Long prevOrderId);

    @Select({"<script>" +
            " SELECT top 1 a.Index_ID " +
            " FROM TMS_OrderIndex a WHERE a.Index_ID in " +
            "<foreach item='item' index='index' collection='orderIds' open='(' separator=',' close=')'>" +
            "#{item}" +
            "</foreach>" +
            " and a.Index_TransportMode = 2 " +
            "</script>"})
    @DataScope(column = TENANT_COLUMN, alias = "a")
    Long getFullCombine(@Param("orderIds") List<Long> orderIds);

    @Select(QUERY_SQL_ID + ORDER_WHERE + " and a.Index_CreateTime > #{startTime} and a.Index_CreateTime < #{endTime} ")
    List<Long> getOrderIdByTime(@Param("startTime") String startTime, @Param("endTime") String endTime);

    @Select(QUERY_SQL_ID + ORDER_WHERE + " and a.Index_CreatorCompanyID = #{tenantId} and a.Index_CreateTime > #{startTime} and a.Index_CreateTime < #{endTime} ")
    List<Long> getOrderIdByTenantIdTime(@Param("tenantId") Long tenantId, @Param("startTime") String startTime, @Param("endTime") String endTime);

    @Select(QUERY_SQL_ID + COMBINE_WHERE + " and a.Index_CreateTime > #{startTime} and a.Index_CreateTime < #{endTime} ")
    List<Long> getCombineIdByTime(@Param("startTime") String startTime, @Param("endTime") String endTime);

    @Select(QUERY_SQL_ID + COMBINE_WHERE + " and a.Index_CreatorCompanyID = #{tenantId} and a.Index_CreateTime > #{startTime} and a.Index_CreateTime < #{endTime} ")
    List<Long> getCombineIdByTenantIdTime(@Param("tenantId") Long tenantId, @Param("startTime") String startTime, @Param("endTime") String endTime);

    @Select(QUERY_SQL_SRC_CLASS + "WHERE a.Index_ID = #{orderId}")
    Long getSrcClassById(Long orderId);

    @Select("SELECT a.Index_CreatorCompanyID,a.Index_CustomerID,b.Index_CustomerName as indexCustomername,CASE when d.Customer_OwnerCompany=d.Customer_CompanyID THEN 2 ELSE d.Customer_UnderLine end as customerType,a.Index_Creator from TMS_OrderIndex a " + LEFT_JOIN + " WHERE a.Index_ID = #{orderId}")
    OrderIndexDO getCompanyById(Long orderId);

    @Select("SELECT Index_ID, Index_GUID, opt_status, Index_Code, Index_PactCode, Index_EndUserID, Index_EndUserName, Index_FromManID, Index_FromMan, Index_From, Index_FromProvince, Index_FromCity, Index_FromDistrict, Index_FromTime, Index_ToManID, Index_ToMan, Index_To, Index_ToProvince, Index_ToCity, Index_ToDistrict, Index_ToTime, Index_TransportMode, Index_GoodsCategory, Index_PackageMode, Index_ChargeMode, Index_PriceUnit, Index_Status, Index_StatusTime, Index_SrcOrderID, Index_RootOrderID, Index_SrcClass, Index_Kms, Index_CarType, Index_CarLength, Index_DriverID, Index_CarID, Index_SupplierID, Index_SupplierCompanyID, Index_CustomerID, Index_CustomerCompanyID, Index_ShipMode, Index_Pick, Index_Delivery, Index_Creator, Index_CreatorCompanyID, Index_CreateTime, Index_Confirmer, Index_ConfirmTime, Index_Singer, Index_SignTime, Index_ReceiptDoc, Index_Exception, Index_Invalid, Index_Comments, Index_OnLoad, Index_OffLoad, Index_Insurance, Index_VolumeAddition, Index_WeightAddition, Index_Description, Index_CarVolume, Index_CarWeight, Index_Combined, Index_CustomerSymbolID, Index_SupplierSymbolID, Index_ReceiptDoc1, Index_ReceiptDoc2, Index_ReceiptDoc3, Index_FromContact, Index_ToContact, Index_GoodsValue, Index_PrevOrderID, Index_DeviceCode, Index_Payment, Index_Payable, Index_GoodsLst, Index_RealFromTime, Index_RealToTime, Index_ReceiptDoc4, Index_ReceiptDoc5, Index_ReceiptDoc6, Index_ReceiptDoc7, Index_RollBack, Index_ReceipTime, Index_FromOperator, Index_TerminalOrderID, Index_TerminalOrderCode, Index_CustomerName, Index_SupplierName, Index_CreatorCompanyName, Index_CustomerSymbolName, Index_SupplierSymbolName, Index_TotalCost, Index_TotalAmount, Index_TotalWeight, Index_TotalVolume, Index_CombinedAmount, Index_CombinedCost, Index_CombinedWeight, Index_CombinedVolume, Index_CombinedOrderAmount, Index_CombinedFrom, Index_CombinedTo, Index_TCacheReady, Index_StartMsgTime, Index_ArriveMsgTime, Index_VerifyCode, Index_DeviceCreatTime, Index_GPSStartTime, Index_DeviceBindingTime, Index_FromLocation, Index_ToLocation, Index_ContainsBillDay, Index_TrackType, Index_SupplierType, Index_CusRollBack, Index_Fromtype, Index_Totype, Index_RollBackOrderID, Index_tpPrint, Index_ExceptionType, Index_AssessLevel, Index_AssessMent, Index_CloseMark, Index_CloseMent, Index_Addorder, Index_AddPacth, Index_CarCount, Index_AutoSchedule, Index_CombinePrice, Index_OrderType, Index_BeSplit, Index_SplitType, Index_ReceiptDoc8, Index_ReceiptDoc9, Index_CloseInitiator, Index_CloseType, Index_signinfotype, Index_signinfo, Index_RollBckLst, Index_VerifySiginTime, Index_CreateType, Index_ReceiptType, Index_additionTransportType, Index_WithChildType, Index_DeviceCode2, Index_Distance, Index_RealFromStatus, Index_DeviceBindingLog, Index_DeviceBindingPart, Index_Test, index_echo " +
            "FROM TMS_OrderIndex " +
            "WHERE Index_ID = #{orderId}")
    OrderIndexDO getByOrderId(Long orderId);
}
